/*
 * Copyright (C) 2008 Google Inc.
 * 
 * Licensed under the Apache License, Version 2.0 (the "License"); you may not
 * use this file except in compliance with the License. You may obtain a copy of
 * the License at
 * 
 * http://www.apache.org/licenses/LICENSE-2.0
 * 
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
 * WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the
 * License for the specific language governing permissions and limitations under
 * the License.
 */

package de.sdroege.memorizer;

import java.util.Iterator;

import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.test.IsolatedContext;
import android.util.Log;
import android.webkit.DownloadListener;

/**
 * Simple notes database access helper class. Defines the basic CRUD operations
 * for the notepad example, and gives the ability to list all notes as well as
 * retrieve or modify a specific note.
 * 
 * This has been improved from the first version of this tutorial through the
 * addition of better error handling and also using returning a Cursor instead
 * of using a collection of inner classes (which is less scalable and not
 * recommended).
 */
public class CardBoxDbAdapter
{

  /**
   * Some String constants to name the table columns The columns have the same
   * name as the JSON Keys
   */
  public static final String DATABASE_NAME = "CardBoxes";
  public static final String DB_TABLE_CARDBOX = "CardBox";
  public static final String DB_TABLE_CARDS = "Card";

  public static final String KEY_NUMOFLEARNED = "numOfLearned";
  public static final String KEY_NUMOFCARDS = "numOfCards";
  public static final String KEY_FOLDERNAME = "folderName";
  public static final String KEY_FOLDERKEY = "folderKey";
  public static final String KEY_CARDBOXDESCRIPTION = "cardBoxDescription";
  public static final String KEY_CARDBOXNAME = "cardBoxName";
  public static final String KEY_CARDBOXNBR = "cardBoxNbr";

  public static final String KEY_DAYSTOWAIT = "daystowait";
  public static final String KEY_LASTPLAYED = "lastPlayed";
  public static final String KEY_KNOWNINROW = "knownInRow";
  public static final String KEY_TIMESNOTKNOWN = "timesNotKnown";
  public static final String KEY_TIMESKNOWN = "timesKnown";
  public static final String KEY_UPDATED = "updated";
  public static final String KEY_CREATED = "created";
  public static final String KEY_ANSWERSOUND = "answerSound";
  public static final String KEY_QUESTIONSOUND = "questionSound";
  public static final String KEY_ANSWERIMAGE = "answerImage";
  public static final String KEY_QUESTIONIMAGE = "questionImage";
  public static final String KEY_ANSWER = "answer";
  public static final String KEY_QUESTION = "question";
  public static final String KEY_SELECTEDFORLEARNING = "selectedForLearning";
  public static final String KEY_CARDNBR = "cardNbr";

  private static final String TAG = "CardBoxDbAdapter"; // Tag name for logcat
  private static long mRevision;
  private DatabaseHelper mDbHelper;
  private SQLiteDatabase mDb;

  private static final int DATABASE_VERSION = 11;

  /**
   * Database creation sql statement
   */
  private static final String DATABASE_CREATE =
  // Table for the CardBoxes
  "CREATE TABLE " + DB_TABLE_CARDBOX + " (" + "_id" + " INTEGER PRIMARY KEY AUTOINCREMENT, " + KEY_NUMOFLEARNED
      + " INTEGER, " + KEY_NUMOFCARDS + " INTEGER, " + KEY_FOLDERNAME + " TEXT, " + KEY_FOLDERKEY + " INTEGER, "
      + KEY_CARDBOXDESCRIPTION
      + " TEXT, "
      + KEY_CARDBOXNAME
      + " TEXT NOT NULL, "
      + KEY_CARDBOXNBR
      + " INTEGER NOT NULL); "
      +

      // Table for the Cards
      "CREATE TABLE " + DB_TABLE_CARDS + " (	" + "_id" + " INTEGER PRIMARY KEY AUTOINCREMENT, " + KEY_DAYSTOWAIT
      + " INTEGER, " + KEY_LASTPLAYED + " TEXT, " + KEY_KNOWNINROW + " INTEGER, " + KEY_TIMESNOTKNOWN + " INTEGER, "
      + KEY_TIMESKNOWN + " INTEGER, " + KEY_UPDATED + " TEXT, " + KEY_CREATED + " TEXT, " + KEY_ANSWERSOUND + " TEXT, "
      + KEY_QUESTIONSOUND + " TEXT, " + KEY_ANSWERIMAGE + " TEXT, " + KEY_QUESTIONIMAGE + " TEXT, " + KEY_ANSWER
      + " TEXT, " + KEY_QUESTION + " TEXT, " + KEY_SELECTEDFORLEARNING
      + " INTEGER, "
      +
      // A card always belongs to a cardbox, so we reference the
      // cardboxnbr from the CardBox table here:
      KEY_CARDBOXNBR + " INTEGER FOREIGN KEY REFERENCES " + DB_TABLE_CARDBOX + "(" + KEY_CARDBOXNBR + "), "
      + KEY_CARDNBR + " INTEGER NOT NULL); ";

  private final Context mCtx;

  private static class DatabaseHelper extends SQLiteOpenHelper
  {

    DatabaseHelper(Context context)
    {
      super( context, DATABASE_NAME, null, DATABASE_VERSION );
    }

    @Override
    public void onCreate(SQLiteDatabase db)
    {

      db.execSQL( DATABASE_CREATE );
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
    {
      Log.w( TAG, "Upgrading database from version " + oldVersion + " to " + newVersion
          + ", which will destroy all old data" );
      db.execSQL( "DROP TABLE IF EXISTS " + DB_TABLE_CARDBOX );
      db.execSQL( "DROP TABLE IF EXISTS " + DB_TABLE_CARDS );
      onCreate( db );
    }
  }

  /**
   * Constructor - takes the context to allow the database to be opened/created
   * 
   * @param ctx
   *          the Context within which to work
   */
  public CardBoxDbAdapter(Context ctx)
  {
    this.mCtx = ctx;
  }

  /**
   * Open the Cardboxes database. If it cannot be opened, try to create a new
   * instance of the database. If it cannot be created, throw an exception to
   * signal the failure
   * 
   * @return this (self reference, allowing this to be chained in an
   *         initialization call)
   * @throws SQLException
   *           if the database could be neither opened or created
   */
  public CardBoxDbAdapter open() throws SQLException
  {
    mDbHelper = new DatabaseHelper( mCtx );
    mDb = mDbHelper.getWritableDatabase();
    return this;
  }

  public void close()
  {
    mDbHelper.close();
  }

  /**
   * Delete the note with the given rowId
   * 
   * @param rowId
   *          id of note to delete
   * @return true if deleted, false otherwise
   */
  // public boolean deleteNote(long rowId) {
  //
  // return mDb.delete(DATABASE_TABLE, KEY_ROWID + "=" + rowId, null) > 0;
  // }

  /**
   * Return a Cursor over the list of all notes in the database
   * 
   * @return Cursor over all notes
   */
  // public Cursor fetchAllNotes() {
  //
  // return mDb.query(DATABASE_TABLE, new String[] {KEY_ROWID, KEY_TITLE,
  // KEY_BODY}, null, null, null, null, null);
  // }

  public Cursor fetchAllCBoxes()
  {
    return mDb.query( DB_TABLE_CARDBOX, new String[] { "_id", KEY_CARDBOXNAME }, null, null, null, null, null );
  }

  /**
   * Return a Cursor positioned at the note that matches the given rowId
   * 
   * @param rowId
   *          id of note to retrieve
   * @return Cursor positioned to matching note, if found
   * @throws SQLException
   *           if note could not be found/retrieved
   */
  // public Cursor fetchNote(long rowId) throws SQLException {
  //
  // Cursor mCursor =
  //
  // mDb.query(true, DATABASE_TABLE, new String[] {KEY_ROWID,
  // KEY_TITLE, KEY_BODY}, KEY_ROWID + "=" + rowId, null,
  // null, null, null, null);
  // if (mCursor != null) {
  // mCursor.moveToFirst();
  // }
  // return mCursor;
  //
  // }
  

  /**
   * Update an existing database entry, or create it. This method takes an
   * JSONObject and parses it for Ediscio Cardbox or Cards data. If there exists
   * already an entry with the same CardNbr or CardBoxNbr, it updates the entry
   * in the database. If there is no entry with that number, it creates a new
   * entry.
   * 
   * @param _CBox
   *          The JSONObject to be parsed.
   * @see EdiscioClient
   */
  public void updateOrCreate(JSONObject _CBox)
  {
    ContentValues data;
    final JSONArray jsonDataArray;
    JSONObject jsonData;
    final String table;
    final String whereClause;
    try
    {
      // Do we have Cardboxes or Cards?
      if ( _CBox.has( "cardBoxes" ) )
      { // We have an Array of CardBoxes
        jsonDataArray = _CBox.getJSONArray( "cardBoxes" );
        table = DB_TABLE_CARDBOX;
        whereClause = KEY_CARDBOXNBR;

      } else
      // if (_CBox.has("cardBox"))
      { // We have an Array of Cards
        jsonDataArray = _CBox.getJSONArray( "cardBox" );
        table = DB_TABLE_CARDS;
        whereClause = KEY_CARDNBR;
      }

      int length = jsonDataArray.length();
      for ( int i = 0; i < length; i++ )
      {
        data = new ContentValues();
        jsonData = jsonDataArray.getJSONObject( i );

        Iterator<String> keys = jsonData.keys();
        String key;
        while ( keys.hasNext() )
        {
          key = keys.next();
          data.put( key, jsonData.getString( key ) );
        }
        // String wheretoersetz = whereClause + "=" +
        // data.getAsString(whereClause)+"\0";
        // int blabla = -1;
        // blabla = mDb.update(table, data, wheretoersetz, null);
        if ( mDb.update( table, data, whereClause + "=" + data.getAsString( whereClause ) + "\0", null ) == 0 )
          mDb.insert( DB_TABLE_CARDBOX, null, data );
      }
    } catch ( JSONException e )
    {
      Log.d( TAG, e.getMessage() );
    }
    

  }

  public void deleteCBox(int CardBoxNbr)
  {
    mDb.delete( DB_TABLE_CARDS, KEY_CARDBOXNBR + "=" + CardBoxNbr, null );
    mDb.delete( DB_TABLE_CARDBOX, KEY_CARDBOXNBR + "=" + CardBoxNbr, null );
  }

}
